﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SQLite;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SQLiteDemo
{
    public partial class MainForm : Form
    {
        public MainForm()
        {
            InitializeComponent();
        }

        private SQLiteHelper SQLiteHelper = new SQLiteHelper();


        private void MainForm_Load(object sender, EventArgs e)
        {
            try
            {
                InitData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            
        }

        private void btnAdd_Click(object sender, EventArgs e)
        {
            string name = txtName.Text;
            if (!string.IsNullOrEmpty(name))
            {
                //判重
                string strSql = @"select count(ID) from tb_User where Name=@Name";
                SQLiteParameter[] parameters = new SQLiteParameter[]
                {
                    new SQLiteParameter("@Name",name.Trim()),
                };
                int count = Convert.ToInt32(SQLiteHelper.ExecuteScalar(strSql, parameters));
                if (count > 0)
                {
                    MessageBox.Show("Name重复，请重新输入!");
                    txtId.Text = "";
                    txtName.Text = "";
                    return;
                }

                strSql = @"INSERT INTO tb_User (Name) VALUES (@Name)";
                int result = SQLiteHelper.ExecuteNonQuery(strSql, parameters);
                if (result == 0)
                {
                    MessageBox.Show("新增失败!");
                }
                else
                {
                    MessageBox.Show("新增成功!");
                    txtName.Text = "";
                    InitData();
                }
            }
            else
            {
                MessageBox.Show("请输入Name!");
            }
        }

        private void btnEdit_Click(object sender, EventArgs e)
        {
            string id = txtId.Text;
            string name = txtName.Text;
            if (string.IsNullOrEmpty(id))
            {
                MessageBox.Show("请先选择一条记录!");
                return;
            }
            if (!string.IsNullOrEmpty(name))
            {
                string strSql = @"Update tb_User Set Name=@Name where ID=@ID";
                SQLiteParameter[] parameters = new SQLiteParameter[]
                {
                    new SQLiteParameter("@Name",name.Trim()),
                    new SQLiteParameter("@ID",id.Trim()),
                };
                int result = SQLiteHelper.ExecuteNonQuery(strSql, parameters);
                if (result == 0)
                {
                    MessageBox.Show("修改失败!");
                }
                else
                {
                    MessageBox.Show("修改成功!");
                    txtId.Text = ""; 
                    txtName.Text = "";
                    InitData();
                }
            }
            else
            {
                MessageBox.Show("请输入Name!");
            }
        }

        private void btnDel_Click(object sender, EventArgs e)
        {
            string id = txtId.Text;
            string name = txtName.Text;
            if (string.IsNullOrEmpty(id))
            {
                MessageBox.Show("请先选择一条记录!");
                return;
            }

            string strSql = @"Delete from tb_User where ID=@ID";
            SQLiteParameter[] parameters = new SQLiteParameter[]
            {
                new SQLiteParameter("@ID",id.Trim()),
            };
            int result = SQLiteHelper.ExecuteNonQuery(strSql, parameters);
            if (result == 0)
            {
                MessageBox.Show("删除失败!");
            }
            else
            {
                MessageBox.Show("删除成功!");
                txtId.Text = ""; 
                txtName.Text = "";
                InitData();
            }
        }

        private void dgvShow_CellClick(object sender, DataGridViewCellEventArgs e)
        {
            //获得当前选中的行   
            int rowindex = e.RowIndex;
            //获得当前行的第0列的值
            txtId.Text = dgvShow.Rows[rowindex].Cells[0].Value.ToString();
            //获得当前行的第一列的值
            txtName.Text = dgvShow.Rows[rowindex].Cells[1].Value.ToString();
        }


        private void InitData()
        {
            string strSql = @"select * from tb_User";
            dgvShow.DataSource = SQLiteHelper.ExecuteList<Model.User>(strSql, null);
            if (dgvShow.Rows.Count > 0)
            {
                dgvShow.Rows[0].Selected = false;
            }
        }






    }
}
